﻿using ICPFrameLib.Business;
using ICPFrameLib.Attributes;
using XHRPMIS.Common;
using EFFC.Frame.Net.Base.Common;
using EFFC.Frame.Net.Base.Data.Base;
using EFFC.Frame.Net.Module.Extend.EWRA.Attributes;
using EFFC.Frame.Net.Module.Extend.EWRA.Attributes.Validation;
using EFFC.Frame.Net.Unit.DB.Parameters;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using EFFC.Extends.LinqDLR2SQL;
using EFFC.Frame.Net.Unit.DB;
using EFFC.Frame.Net.Base.Data;
using System.IO;
using EFFC.Frame.Net.Base.Constants;
using System.Text.RegularExpressions;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System.Text;
using ICPFrameLib.Common;
using EFFC.Frame.Net.Unit.DB.Datas;

namespace XHRPMIS.Business.v1._0
{
    public partial class MetaDynamicForm
    {
        #region OpenAPI
        [EWRARouteDesc("执行资料汇总查询")]
        [EWRARoute("post", "/md_form/{id}/summary")]
        [EWRAAddInput("id", "string", "元数据表uid", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.Path, false)]
        [EWRAAddInput("summary_column", "string", @"汇总栏位多个逗号分隔", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("query_column", "string", @"查询栏位多个逗号分隔", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("filter_express", "string", @"固定过滤条件表达式，采用DBExpress的$where表达式写法，传入时采用base64+urlcode编码方式；
表达式中可以使用一些基础变量，如下：
__login_id:当前登录者ID,
__login_name:当前登录者的登录名称
__user_no:登录信息中的userno值,
__no:登录信息中的no值,
__user_name:登录信息中的username值,
__user_type:登录信息中的usertype值,
__role_no:当前登录者系统角色编号，多个,
__role_name:当前登录者系统角色名称，多个,
__now:当前时间，格式yyyy-MM-dd HH:mm:ss
", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("fixed_orderby_express", "string", "固定排序表达式，采用DBExpress的$orderby表达式写法，传入时采用base64+urlcode编码方式", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("group_column", "string", "分组栏位，多个用逗号分隔", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAOutputDesc("返回结果", @"{
code:'success-成功，failed-失败',
msg:'提示信息',
data:[汇总之后的资料]
}")]
        public object Sum(string id)
        {
            string summary_column = ComFunc.nvl(PostDataD.summary_column);
            string query_columns = ComFunc.nvl(PostDataD.query_column);
            string filter_express = ComFunc.UrlDecode(ComFunc.IsBase64Then(PostDataD.filter_express));
            string fixed_orderby_express = ComFunc.UrlDecode(ComFunc.IsBase64Then(PostDataD.fixed_orderby_express));
            string group_column = ComFunc.UrlDecode(ComFunc.IsBase64Then(PostDataD.group_column));

            var orderby_fixed_filter_express = FrameDLRObject.IsJsonThen(fixed_orderby_express, null, FrameDLRFlags.SensitiveCase);
            var where_filter_express = parseFixedExpress(filter_express);

            if (filter_express != "" && where_filter_express == null)
            {
                return new
                {
                    code = "failed",
                    msg = "filter_express格式不正确"
                };
            }
            if (fixed_orderby_express != "" && orderby_fixed_filter_express == null)
            {
                return new
                {
                    code = "failed",
                    msg = "fixed_orderby_express格式不正确"
                };
            }

            var metaup = DB.NewMetaUnitParameter();
            var up = DB.NewDBUnitParameter();

            var metainfo = MetaDataUtilities.GetAllTables( metaup).Where(w=>w.MetaName == id || w.MetaUID == id).FirstOrDefault();
            if (metainfo == null) return new
            {
                code = "failed",
                msg = "无该元数据"
            };
            if (!metainfo.IsCreated)
            {
                return new
                {
                    code = "failed",
                    msg = "该元数据未创建"
                };
            }
            var columns = metainfo.columns;
            // 固定带上以下栏位
            columns.AddRange(MetaDataUtilities.FixColumnsModel);
            //过滤要抓取的栏位
            var q_columns = query_columns.Split(",", StringSplitOptions.RemoveEmptyEntries);
            columns = q_columns.Length <= 0 ? new List<MetaDataUtilities.MetaColumn>() : columns.Where(d => q_columns.Contains(d.MetaColumnName)).ToList();

            var arr_sum_columns = summary_column.Split(",", StringSplitOptions.RemoveEmptyEntries);
            var s_columns = arr_sum_columns.Length <= 0 ? new List<MetaDataUtilities.MetaColumn>() : columns.Where(d => arr_sum_columns.Contains(d.MetaColumnName)).ToList();

            FrameDLRObject express = FrameDLRObject.CreateInstance(FrameDLRFlags.SensitiveCase);
            express.SetValue("$acttype", "Query");
            express.SetValue("$table", metainfo.MetaName);
            foreach(var c in s_columns)
            {
                express.SetValue($"sum_{c}", $"#sum:{c}");
            }
            foreach(var c in columns)
            {
                express.SetValue(c.MetaColumnName, true);
            }
            if (!string.IsNullOrEmpty(group_column))
            {
                express.SetValue("$groupby", group_column);
            }
            FrameDLRObject where = where_filter_express == null ? FrameDLRObject.CreateInstance(FrameDLRFlags.SensitiveCase) : where_filter_express;
            if(where.Items.Count > 0)
            {
                express.SetValue("$where", where);
            }
            FrameDLRObject orderby = orderby_fixed_filter_express == null ? FrameDLRObject.CreateInstance(FrameDLRFlags.SensitiveCase) : orderby_fixed_filter_express;
            express.SetValue("$orderby", orderby);

            var result = DB.Excute(up, express, true);
            var resultlist = result.QueryData<FrameDLRObject>();

            return new
            {
                code = "success",
                msg = "",
                data = resultlist
            };
        }
        [EWRARouteDesc("执行资料合计查询")]
        [EWRARoute("post", "/md_form/{id}/count")]
        [EWRAAddInput("id", "string", "元数据表uid", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.Path, false)]
        [EWRAAddInput("query_column", "string", @"查询栏位多个逗号分隔", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("filter_express", "string", @"固定过滤条件表达式，采用DBExpress的$where表达式写法，传入时采用base64+urlcode编码方式；
表达式中可以使用一些基础变量，如下：
__login_id:当前登录者ID,
__login_name:当前登录者的登录名称
__user_no:登录信息中的userno值,
__no:登录信息中的no值,
__user_name:登录信息中的username值,
__user_type:登录信息中的usertype值,
__role_no:当前登录者系统角色编号，多个,
__role_name:当前登录者系统角色名称，多个,
__now:当前时间，格式yyyy-MM-dd HH:mm:ss
", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("fixed_orderby_express", "string", "固定排序表达式，采用DBExpress的$orderby表达式写法，传入时采用base64+urlcode编码方式", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAAddInput("group_column", "string", "分组栏位，多个用逗号分隔", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, true)]
        [EWRAOutputDesc("返回结果", @"{
code:'success-成功，failed-失败',
msg:'提示信息',
data:[汇总之后的资料]
}")]
        public object Count(string id)
        {
            string query_columns = ComFunc.nvl(PostDataD.query_column);
            string filter_express = ComFunc.UrlDecode(ComFunc.IsBase64Then(PostDataD.filter_express));
            string fixed_orderby_express = ComFunc.UrlDecode(ComFunc.IsBase64Then(PostDataD.fixed_orderby_express));
            string group_column = ComFunc.nvl(PostDataD.group_column);

            var orderby_fixed_filter_express = FrameDLRObject.IsJsonThen(fixed_orderby_express, null, FrameDLRFlags.SensitiveCase);
            var where_filter_express = parseFixedExpress(filter_express);

            if (filter_express != "" && where_filter_express == null)
            {
                return new
                {
                    code = "failed",
                    msg = "filter_express格式不正确"
                };
            }
            if (fixed_orderby_express != "" && orderby_fixed_filter_express == null)
            {
                return new
                {
                    code = "failed",
                    msg = "fixed_orderby_express格式不正确"
                };
            }

            var metaup = DB.NewMetaUnitParameter();
            var up = DB.NewDBUnitParameter();

            var metainfo = MetaDataUtilities.GetAllTables( metaup).Where(w => w.MetaName == id || w.MetaUID == id).FirstOrDefault();
            if (metainfo == null) return new
            {
                code = "failed",
                msg = "无该元数据"
            };
            if (!metainfo.IsCreated)
            {
                return new
                {
                    code = "failed",
                    msg = "该元数据未创建"
                };
            }
            var columns = metainfo.columns;
            // 固定带上以下栏位
            columns.AddRange(MetaDataUtilities.FixColumnsModel);
            //过滤要抓取的栏位
            var q_columns = query_columns.Split(",", StringSplitOptions.RemoveEmptyEntries);
            columns = q_columns.Length <= 0 ? new List<MetaDataUtilities.MetaColumn>() : columns.Where(d => q_columns.Contains(d.MetaColumnName)).ToList();

            FrameDLRObject express = FrameDLRObject.CreateInstance(FrameDLRFlags.SensitiveCase);
            express.SetValue("$acttype", "Query");
            express.SetValue("$table", metainfo.MetaName);
            express.SetValue("summary_count", "#sql:count(1)");
            foreach (var c in columns)
            {
                express.SetValue(c.MetaColumnName, true);
            }
            if (!string.IsNullOrEmpty(group_column))
            {
                express.SetValue("$groupby", group_column);
            }
            FrameDLRObject where = where_filter_express == null ? FrameDLRObject.CreateInstance(FrameDLRFlags.SensitiveCase) : where_filter_express;
            if (where.Items.Count > 0)
            {
                express.SetValue("$where", where);
            }
            FrameDLRObject orderby = orderby_fixed_filter_express == null ? FrameDLRObject.CreateInstance(FrameDLRFlags.SensitiveCase) : orderby_fixed_filter_express;
            express.SetValue("$orderby", orderby);

            var result = DB.Excute(up, express, true);
            var resultlist = result.QueryData<FrameDLRObject>();
            SetRefColumnsText(metaup, up, metainfo, columns, resultlist);

            return new
            {
                code = "success",
                msg = "",
                data = resultlist
            };
        }
        [EWRARoute("post", "/md_form/bi/query")]
        [EWRARouteDesc("BI分析查询")]
        [EWRAEmptyValid("expr")]
        [EWRAAddInput("limit", "int", "每页笔数，如果要查全部结果，请用1000等较大的值，为性能考虑尽量用较小的值", "默认为10", EFFC.Frame.Net.Base.Constants.RestInputPosition.QueryString, true)]
        [EWRAAddInput("page", "int", "指定页数", "默认为1", EFFC.Frame.Net.Base.Constants.RestInputPosition.QueryString, true)]
        [EWRAAddInput("expr", "string", @"表达式，json结构，先UrlCode再采用base64编码，结构如下：{
    id:""指定的table名称"",
    as:""别名"",
    join:'Join表达式，符合DBExpress中的$table中join部分',
    dimension:{
      '{key}':'维度表达式,维度被用于分组识别，比如：年度、季度等,符合DBExpress中的column的表达式'
    },
    measurement:{
      '{key}':'量度度表达式,量度属于统计的显示结果，如：sum,count,max,min等，符合DBExpress中的column的表达式'
    },
    'where':'条件表达式，符合DBExpress中的where表达式',
    'orderby':'排序表达式,使用维度中的key，多个用逗号分隔，格式:{key1} [asc|desc],{key2} [asc|desc],...'
};
表达式中可以使用一些基础变量，如下：
__login_id:当前登录者ID,
__login_name:当前登录者的登录名称
__user_no:登录信息中的userno值,
__no:登录信息中的no值,
__user_name:登录信息中的username值,
__user_type:登录信息中的usertype值,
__role_no:当前登录者系统角色编号，多个,
__role_name:当前登录者系统角色名称，多个,
__now:当前时间，格式yyyy-MM-dd HH:mm:ss", "无默认值", EFFC.Frame.Net.Base.Constants.RestInputPosition.PostData, false)]
        [EWRAOutputDesc("返回结果", @"{
code:""success-成功，failed-失败"",
msg:""提示信息"",
data:{
        Count_Of_OnePage:'每页笔数',
        CurrentPage: '当前页数',
        TotalPage:'总页数',
        TotalRow:'总笔数',
        QueryDatas:[数据集]
    }
}")]
        public object BIQuery()
        {
            SetCacheEnable(false);

            var exprstr = ComFunc.UrlDecode(ComFunc.IsBase64Then(PostDataD.expr));
            if (exprstr == "")
            {
                return new
                {
                    code = "failed",
                    msg = "表达式格式不正确"
                };
            }
            var up = DB.NewDBUnitParameter();
            var metaup = DB.NewMetaUnitParameter();
            UnitDataCollection result = null;
            var msg = "";

            if (DoQueryByBIExpress(up, metaup, exprstr, ref msg, ref result))
            {
                return ResultHelper.DFormQueryByPageFormat(result);
            }
            else
            {
                return new
                {
                    code = "failed",
                    msg
                };
            }
        }
        #endregion

        #region LocalFunction
        /// <summary>
        /// 执行BI表达式查询操作
        /// </summary>
        /// <param name="up"></param>
        /// <param name="metaup"></param>
        /// <param name="exprstr">结构如下：{
        /// id:""指定的table名称"",
        /// as:""别名"",
        ///  join:'Join表达式，符合DBExpress中的$table中join部分',
        /// dimension:{
        /// '{key}':'维度表达式,维度被用于分组识别，比如：年度、季度等,符合DBExpress中的column的表达式'
        /// },
        /// measurement:{
        /// '{key}':'量度度表达式,量度属于统计的显示结果，如：sum,count,max,min等，符合DBExpress中的column的表达式'
        /// },
        /// 'where':'条件表达式，符合DBExpress中的where表达式',
        /// 'orderby':'排序表达式,使用维度中的key，多个用逗号分隔，格式:{key1} [asc|desc],{key2} [asc|desc],...'
        /// }
        /// 表达式中可以使用一些基础变量，如下：
        /// __login_id:当前登录者ID,
        /// __login_name:当前登录者的登录名称
        /// __user_no:登录信息中的userno值,
        /// __no:登录信息中的no值,
        /// __user_name:登录信息中的username值,
        /// __user_type:登录信息中的usertype值,
        /// __role_no:当前登录者系统角色编号，多个,
        /// __role_name:当前登录者系统角色名称，多个,
        /// __now:当前时间，格式yyyy-MM-dd HH:mm:ss</param>
        /// <param name="msg"></param>
        /// <param name="result"></param>
        /// <param name="is_log"></param>
        /// <returns></returns>
        public bool DoQueryByBIExpress(UnitParameter up, UnitParameter metaup, string exprstr, ref string msg, ref UnitDataCollection result, bool is_log = false)
        {
            dynamic expr = parseFixedExpress(exprstr);
            if (expr == null)
            {
                msg = "表达式格式不正确";
                return false;
            }

            var re = BIExpress.Parse(exprstr);
            if (!re.IsSuccess)
            {
                msg = re.Msg;
                return false;
            }
            FrameDLRObject dimension = (FrameDLRObject)expr.dimension;
            FrameDLRObject measurement = (FrameDLRObject)expr.measurement;
            string orderby = ComFunc.nvl(expr.orderby);
            FrameDLRObject where = expr.where != null ? (expr.where as FrameDLRObject) : null;

            var tableinfo = MetaDataUtilities.GetAllTables(metaup).Where(w => w.MetaName == re.Result.Target || w.MetaUID == re.Result.Target).FirstOrDefault();
            if (tableinfo == null)
            {
                msg = "无该元数据模型";
                return false;
            }
            foreach(var t in re.Result.Join)
            {
                if(MetaDataUtilities.GetAllTables(metaup).Where(w => w.MetaName == t.Target || w.MetaUID == t.Target).Count() <= 0)
                {
                    msg = $"Join Table中无{t.Target}数据模型";
                    return false;
                }
            }
            FrameDLRObject express = re.Result.ToDBExpress();
            result = DB.Excute(up, express, is_log);
            return true;
        }
        #endregion
    }
}
